package com.neuedu;

import com.opencsv.CSVReader;
import com.opencsv.CSVWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Random;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ChinaChargingPileClean {

    public static void main(String[] args) {
        String inputFilePath = "C:\\Users\\29874\\Desktop\\ChinaCharging.csv";
        String outputFilePath = "D:\\B_HtuLearn\\Data\\ChinaChargingCleaned.csv";

        try (CSVReader reader = new CSVReader(new FileReader(inputFilePath));
             CSVWriter writer = new CSVWriter(new FileWriter(outputFilePath),
                     CSVWriter.DEFAULT_SEPARATOR,
                     CSVWriter.NO_QUOTE_CHARACTER,
                     CSVWriter.DEFAULT_ESCAPE_CHARACTER,
                     CSVWriter.DEFAULT_LINE_END)) {

            // 读取 CSV 数据
            List<String[]> allData = new ArrayList<>();
            String[] header = reader.readNext();
            if (header != null) {
                allData.add(header);
            } else {
                System.out.println("Error: No header found.");
                return;
            }

            String[] nextLine;
            while ((nextLine = reader.readNext()) != null) {
                if (nextLine.length < 2) {
                    continue;  // 跳过空行或列数不正确的行
                }
                // 将时间列转换为标准SQL格式：yyyy-MM-dd
                nextLine[0] = convertToSqlDate(nextLine[0]);

                for (int i = 1; i < nextLine.length; i++) {
                    if (nextLine[i] == null || nextLine[i].isEmpty()) {
                        nextLine[i] = generateRandomValueForColumn(allData, i);
                    }
                }
                allData.add(nextLine);
            }

            for (int col = 1; col < allData.get(0).length; col++) {
                List<Integer> columnData = new ArrayList<>();
                for (int i = 1; i < allData.size(); i++) {
                    try {
                        columnData.add(Integer.parseInt(allData.get(i)[col]));
                    } catch (NumberFormatException e) {
                        // 跳过
                    }
                }

                Collections.sort(columnData, Collections.reverseOrder());

                int index = 0;
                for (int i = 1; i < allData.size(); i++) {
                    try {
                        allData.get(i)[col] = String.valueOf(columnData.get(index++));
                    } catch (IndexOutOfBoundsException e) {
                        break;
                    }
                }
            }
            // 将处理后的数据写入新文件
            for (String[] line : allData) {
                writer.writeNext(line);
            }

            System.out.println("文件处理完成，已保存至：" + outputFilePath);
        } catch (IOException e) {
            System.out.println("文件读取错误: " + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("其他错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static String convertToSqlDate(String dateString) {
        SimpleDateFormat inputFormat = new SimpleDateFormat("yyyy/MM/dd");
        SimpleDateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date date = inputFormat.parse(dateString);
            return outputFormat.format(date);
        } catch (Exception e) {
            System.out.println("日期格式转换错误: " + e.getMessage());
            return dateString;
        }
    }

    private static String generateRandomValueForColumn(List<String[]> allData, int columnIndex) {
        List<Integer> columnData = new ArrayList<>();
        for (int i = 1; i < allData.size(); i++) {
            String[] row = allData.get(i);
            try {
                columnData.add(Integer.parseInt(row[columnIndex]));
            } catch (NumberFormatException e) {
                // skip
            }
        }

        if (columnData.isEmpty()) {
            return "0";
        }

        // 计算已有数据的平均值和标准差
        double mean = columnData.stream().mapToInt(Integer::intValue).average().orElse(0);
        double stdDev = calculateStandardDeviation(columnData, mean);

        Random random = new Random();
        int randomValue = (int) (mean + random.nextGaussian() * stdDev);

        if (randomValue < 0) {
            randomValue = 0;
        }

        return String.valueOf(randomValue);
    }

    private static double calculateStandardDeviation(List<Integer> data, double mean) {
        double sum = 0;
        for (int value : data) {
            sum += Math.pow(value - mean, 2);
        }
        return Math.sqrt(sum / data.size());
    }
}
